Import and export CSV file data is mostly used in web based application. Generally, CSV file data is used to import and export tabular data between programs because CSV is most popular file format to store tabular data in plain text.
In this article, i will show you how to export mysqldatabase table records to CSV file using PHP. Also , you will get the knowledge to create CSV file in PHP and download the save MySQL data in CSV file.
Before start export to CSV functionality, we will create table in mysql database to store some users records.
Create Users Table in MySql Database
The following MySql query create a users table with basic fields in database.The Users table holds the all user information which is used to export in CSV file.
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE TABLE `users` ( `id` int(11) NOT NULL, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL, `created` datetime NOT NULL, `modified` datetime NOT NULL, `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `users` -- INSERT INTO `users` (`id`, `name`, `email`, `phone`, `created`, `modified`, `status`) VALUES (1, 'Pradeep', 'pradeep@gmail.com', '9999999999', '2017-08-10 00:00:00', '2017-08-10 00:00:00', '1'), (2, 'Vikas', 'vikas@gmail.com', '9999999999', '2017-08-11 00:00:00', '2017-08-11 00:00:00', '1'), (3, 'Mohit', 'mohit@gmail.com', '988888888', '2017-08-12 00:00:00', '2017-08-12 00:00:00', '0'), (4, 'Jasbir', 'jasbir@gmail.com', '8888888888', '2017-08-13 00:00:00', '2017-08-13 00:00:00', '1'); |
Create MySql database connection
dbconnection.php
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<?php //DB details $dbHost = 'localhost'; $dbUsername = 'root'; $dbPassword = ''; $dbName = 'csvtutorials'; //Create connection and select DB $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); if($db->connect_error){ die("Unable to connect database: " . $db->connect_error); } ?> |
Create index.php file to export data from database using PHP and MySql
index.php
Retrieved user’s data from the database is listed in the HTML table and Export link at the top of the table that navigates to the exportcsvData.php
file to export table data to CSV file.
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
<!DOCTYPE html> <html> <head> <title>How to Export database records to CSV File using PHP and MySQL</title> <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> </head> <body> <div class="container"> <div class="panel panel-default"> <div class="panel-heading"> Users Record <a href="exportcsvData.php" class="btn btn-success pull-right">Export Users Data to CSV File</a> </div> <div class="panel-body"> <table class="table table-bordered"> <thead> <tr> <th>Name</th> <th>Email</th> <th>Phone</th> <th>Created</th> <th>Status</th> </tr> </thead> <tbody> <?php //include database configuration file include 'dbconnection.php'; //get Users records from database $query = $db->query("SELECT * FROM users ORDER BY id DESC"); if($query->num_rows > 0){ while($row = $query->fetch_assoc()){ ?> <tr> <td><?php echo $row['name']; ?></td> <td><?php echo $row['email']; ?></td> <td><?php echo $row['phone']; ?></td> <td><?php echo $row['created']; ?></td> <td><?php echo ($row['status'] == '1')?'Active':'Inactive'; ?></td> </tr> <?php } }else{ ?> <tr><td colspan="5">No member(s) found.....</td></tr> <?php } ?> </tbody> </table> </div> </div> </div> </body> </html> |
exportcsvData.php
In this file, we will retrive data from database and create file pointer using fopen() function. Specify the header columns and put into CSV file.
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
<?php //include database configuration file include 'dbconnection.php'; //get User records from database $query = $db->query("SELECT * FROM users ORDER BY id DESC"); if($query->num_rows > 0){ $delimiter = ","; $filename = "users_" . date('Y-m-d') . ".csv"; //create a file pointer $f = fopen('php://memory', 'w'); //set Table column headers $fields = array('ID', 'Name', 'Email', 'Phone', 'Created', 'Status'); fputcsv($f, $fields, $delimiter); //output each row of the data, format line as csv and write to file pointer while($row = $query->fetch_assoc()){ $status = ($row['status'] == '1')?'Active':'Inactive'; $lineData = array($row['id'], $row['name'], $row['email'], $row['phone'], $row['created'], $status); fputcsv($f, $lineData, $delimiter); } //move back to beginning of file fseek($f, 0); //set headers to download file rather than displayed header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $filename . '";'); //output all remaining data on a file pointer fpassthru($f); } exit; ?> |
Conclusion
In the example script, you will get an easy way to export data to CSV file in PHP. You can modify the export functionality as per your requirements.
Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request
[sociallocker]
[/sociallocker]
Pradeep Maurya is the Professional Web Developer & Designer and the Founder of “Tutorials website”. He lives in Delhi and loves to be a self-dependent person. As an owner, he is trying his best to improve this platform day by day. His passion, dedication and quick decision making ability to stand apart from others. He’s an avid blogger and writes on the publications like Dzone, e27.co